Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Enhancements

You may want to make some additional enhancements to improve the performance of the DSS system. Listed here are some of the items that can offer performance improvement; also listed are a few items that do not improve performance. Each item is accompanied by an explanation.

  Block size. In a DSS system, you may find that increasing the database block size can greatly improve performance. Because many of the queries access the database in a sequential manner, even though concurrent queries randomize the I/O, having a larger block size brings more of the rows into the SGA at once. Having these additional rows in the SGA can benefit you because you will be using them.
  Clusters. Depending on the application, you may benefit from clusters. The benefit you receive depends completely on the data access patterns and your application.
  Hash clusters. Because the DSS system typically uses table scans to get large amounts of data, a hash cluster will most likely degrade performance. Hash clusters are useful when the queries involve equality statements on the hash key.
  Indexes. Again, because table scans are involved in most queries, indexes may not be particularly useful (although some queries may benefit from the use of indexes). How and when you use indexes depends on your application. If you do use indexes, you may want to take advantage of the FULL hint, which bypasses the index, if you know you will be doing a table scan.
  Direct write sorts. Direct write sorts is a new feature that allows you to bypass the SGA when performing sort operations. This feature prevents sorts from taking unnecessary space in the SGA; the result is more SGA space for other operations.
  Parallel Query option. The Oracle Parallel Query option can almost certainly improve the performance of your DSS system. Parallel queries can be performed on sorts, joins, and table scans to improve the performance of those operations.

Many of these enhancements can help your performance; some may degrade performance. The specific effect on your system will vary, but the information given here is true for most general cases.

Parallel Query Option

The DSS system is probably the best application to take advantage of the Parallel Query option. The design of the Parallel Query option is centered around complex queries that access large amounts of data—as is typical of a DSS system. Here is a short list of some of the operations that can be parallelized:

  Sorts
  Joins
  Table scans

All these query types are typically used in a DSS system. Because these queries are typically of long duration and involve large amounts of disk I/O, the time it takes to complete these queries can be cut drastically with the use of the Parallel Query option.

Once you set up a test database and develop some test queries (hopefully similar to, if not exactly the same as, production queries), run these queries with various degrees of parallelism to determine optimal performance.

If you will run these queries with other queries in production, you should also test with multiple queries. A tuning parameter set up for use with a single query may not be optimal when used with many queries in parallel.

The Parallel Query option can take advantage of the concurrent nature of disk arrays. By splitting up the queries into many small pieces, you may be able to request data from all the drives in your system simultaneously, optimizing I/O performance.

Oracle Parallel Server Option

The Oracle Parallel Server option may be very beneficial to your DSS operation in two ways:

1.  Performance. If your system is a candidate for the Parallel Server option, you will see significant performance improvements.
2.  Fault tolerance. By using the Oracle Parallel Server option, you can keep the system running even if a computer fails. DSS systems do not typically have the same uptime requirements as OLTP systems.

The Oracle Parallel Server option can enhance the performance of your DSS system, but only if your system is suited for the Parallel Server option. To see scaleable performance increases, the system must meet the following criteria:

  Partitionable data. The data is partitionable. In other words, the work related to certain tables can be done on one computer while work related to other tables is done on another computer. If all users access the exact same data, your application may not be a good candidate for the Parallel Server option. Although some overlap is fine, the majority of the data should be partitionable.
  Many processes. If you have only a few users and concurrent transactions, you probably are not a good candidate for the Parallel Server option. However, if you use the Parallel Query option, you may use a sufficient number of query server processes to satisfy this requirement.

If these conditions are met, you will probably benefit from using the Oracle Parallel Server option.


NOTE:  The Oracle Parallel Server option is not available on all platforms. Consult your system provider or Oracle to determine which platforms support the Oracle Parallel Server option.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.